package com.cx.util.jdbc;

import com.cx.pojo.User;
import org.junit.Test;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zhangshuai
 * @version V1.0
 * @Project Java2_26
 * @Package com.cx.util.jdbc
 * @Description:
 * @Date 2025/6/5 8:16
 */

public class JDBCTest {

    String url = "jdbc:mysql://localhost:3306/test_db1";
    String username = "root";
    String password = "root";

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        JDBCTest jdbcTest = new JDBCTest();
        jdbcTest.jdbcQueryOne(2);
        User user = new User();
        user.setName("王五");
        user.setAge(20);
        user.setGender("男");
        user.setEmail("123@qq.com");
        user.setCreate_time(new java.util.Date());

        jdbcTest.jdbcInsert(user);
        /*User user = new User();
        user.setId(3);
        user.setName("赵六");
        user.setAge(20);
        user.setGender("女");
        user.setEmail("aaaa@123.com");
        user.setCreate_time(new Date(2025, 6, 5));
        jdbcTest.jdbcUpdate(user);*/

        jdbcTest.jdbcDelete(3);


    }

    //查询所有用户信息
    public  List<User> jdbcQueryList() throws ClassNotFoundException, SQLException {
        List<User> list = new ArrayList<>();


           //2.创建连接
        Connection connection = JDBCUtil.getConnection();
        //3.创建语句
         String sql = "select * from user";

        //4.执行sql对象 Statement  PreparedStatement
        //Statement  会有sql注入的风险  注入的是字符串，会被解析
        //Statement statement = connection.createStatement();
        //PreparedStatement  不会有sql注入的风险  注入的是？占位符
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //5.处理结果
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String gender = resultSet.getString("gender");
            String email = resultSet.getString("email");
            Date create_time = resultSet.getDate("create_time");

            System.out.println(id+"\t"+name+"\t"+age+"\t"+gender+"\t"+email+"\t"+create_time);
            User user = new User(id, name, age, gender, email, create_time);
            list.add(user);
        }
        //6.关闭资源
       JDBCUtil.close(connection,preparedStatement,resultSet);
        System.out.println("数据库连接关闭"+"list集合"+list);
        return list;
    }
    //查询一个用户信息
    public  User jdbcQueryOne(int id) throws ClassNotFoundException, SQLException {

        //1.加载驱动 如果是mysql8.0以上版本，驱动需要加cj,如果是5版本，不需要加cj
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.创建连接
        Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println("数据库连接成功");
        //3.创建语句
        String sql = "select * from user where id = ?";

        //4.执行sql对象 Statement  PreparedStatement
        //Statement  会有sql注入的风险  注入的是字符串，会被解析
        //Statement statement = connection.createStatement();
        //PreparedStatement  不会有sql注入的风险  注入的是？占位符
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //设置参数
        preparedStatement.setInt(1,id);
        User user = null;
        //5.处理结果
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String gender = resultSet.getString("gender");
            String email = resultSet.getString("email");
            Date create_time = resultSet.getDate("create_time");
            System.out.println(id+"\t"+name+"\t"+age+"\t"+gender+"\t"+email+"\t"+create_time);
            user = new User(id, name, age, gender, email, create_time);
        }
        //6.关闭资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
        System.out.println("数据库连接关闭");
        System.out.println("查询到的用户信息："+user);
        return user;
    }
    //添加用户信息
    public void jdbcInsert(User user) throws ClassNotFoundException, SQLException {

        //1.加载驱动 如果是mysql8.0以上版本，驱动需要加cj,如果是5版本，不需要加cj
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.创建连接
        Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println("数据库连接成功");
        //3.创建语句
        String sql = "insert into user(name,age,gender,email,create_time) values(?,?,?,?,?)";

        //4.执行sql对象 Statement  PreparedStatement
        //Statement  会有sql注入的风险  注入的是字符串，会被解析
        //Statement statement = connection.createStatement();
        //PreparedStatement  不会有sql注入的风险  注入的是？占位符
        PreparedStatement preparedStatement = connection.prepareStatement(sql);


        //设置参数
        preparedStatement.setString(1,user.getName());
        preparedStatement.setInt(2,user.getAge());
        preparedStatement.setString(3,user.getGender());
        preparedStatement.setString(4,user.getEmail());
        preparedStatement.setDate(5,new Date(user.getCreate_time().getTime()));

        //5.处理结果
        int result = preparedStatement.executeUpdate();
        if (result>0){
            System.out.println("添加成功");
        }

        //6.关闭资源

        preparedStatement.close();
        connection.close();
        System.out.println("数据库连接关闭");


    }

    public void jdbcUpdate(User user) throws ClassNotFoundException, SQLException {

        //1.加载驱动 如果是mysql8.0以上版本，驱动需要加cj,如果是5版本，不需要加cj
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.创建连接
        Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println("数据库连接成功");
        //3.创建语句
        String sql = "update user set name=?,age=?,gender=?,email=?,create_time=? where id=?";

        //4.执行sql对象 Statement  PreparedStatement
        //Statement  会有sql注入的风险  注入的是字符串，会被解析
        //Statement statement = connection.createStatement();
        //PreparedStatement  不会有sql注入的风险  注入的是？占位符
        PreparedStatement preparedStatement = connection.prepareStatement(sql);


        //设置参数
        preparedStatement.setString(1,user.getName());
        preparedStatement.setInt(2,user.getAge());
        preparedStatement.setString(3,user.getGender());
        preparedStatement.setString(4,user.getEmail());
        preparedStatement.setDate(5,new Date(user.getCreate_time().getTime()));
        preparedStatement.setInt(6,user.getId());

        //5.处理结果
        int result = preparedStatement.executeUpdate();
        if (result>0){
            System.out.println("修改成功");
        }

        //6.关闭资源

        preparedStatement.close();
        connection.close();
        System.out.println("数据库连接关闭");


    }

    public void jdbcDelete(int id) throws ClassNotFoundException, SQLException {

        //1.加载驱动 如果是mysql8.0以上版本，驱动需要加cj,如果是5版本，不需要加cj
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.创建连接
        Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println("数据库连接成功");
        //3.创建语句
        String sql = "delete from user where id=?";

        //4.执行sql对象 Statement  PreparedStatement
        //Statement  会有sql注入的风险  注入的是字符串，会被解析
        //Statement statement = connection.createStatement();
        //PreparedStatement  不会有sql注入的风险  注入的是？占位符
        PreparedStatement preparedStatement = connection.prepareStatement(sql);


        //设置参数

        preparedStatement.setInt(1,id);
        //5.处理结果
        int result = preparedStatement.executeUpdate();
        System.out.println("删除结果："+result);
        if (result>0){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }

        //6.关闭资源
        preparedStatement.close();
        connection.close();
        System.out.println("数据库连接关闭");


    }
}